﻿using BaseFrame.Entity;
using SqlSugar;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Threading.Tasks;

namespace BaseFrame
{
    public class SqlSugarClientOperation
    {
        SqlSugarClient _db;
        public SqlSugarClientOperation()
        {
            _db = GetInstance();
            var types = Assembly.GetExecutingAssembly().GetTypes().Where(it => it.FullName.StartsWith($"{Global.solutionName}.{Global.entityName}"));
            types.ToList().ForEach(type =>
            {
                CreateDatabaseAndTable(false, Global.entityStringDefaultLength, type);
            });
        }

        private SqlSugarClient GetInstance()
        {
            SqlSugarClient db = new SqlSugarClient(new ConnectionConfig()
            {
                ConnectionString = Global.mysqlServer,//连接符字串
                DbType = DbType.MySql,
                IsAutoCloseConnection = true,
            });

            //添加Sql打印事件，开发中可以删掉这个代码
            db.Aop.OnLogExecuting = (sql, pars) =>
            {
                Console.WriteLine(sql);
            };
            db.Aop.OnError = (ex) =>
            {
                Console.WriteLine(ex);
            };
            return db;
        }


        private void CreateDatabaseAndTable(bool Backup = false, int StringDefaultLength = 50, params Type[] types)
        {
            _db.CodeFirst.SetStringDefaultLength(StringDefaultLength);
            _db.DbMaintenance.CreateDatabase();
            if (Backup)
            {
                _db.CodeFirst.BackupTable().InitTables(types);
            }
            else
            {
                _db.CodeFirst.InitTables(types);
            }
        }

        public int GetIntDataBySql(string sql)
        {
            return _db.Ado.GetInt(sql);
        }

        public SqlSugarClient Db()
        {
            return _db;
        }
        public SimpleClient<T> GetCilentDb<T>() where T : class, new()
        {
            return new SimpleClient<T>(_db);
        }
        #region 查询
        public List<T> GetList<T>() where T : class, new()
        {
            return GetCilentDb<T>().AsQueryable().ToList();
        }
        public List<T> GetListDataByWhere<T>(List<IConditionalModel> conditionalModels) where T : class, new()
        {
            return GetCilentDb<T>().AsQueryable().Where(conditionalModels).ToList();
        }
        public ISugarQueryable<T> GetSugarQueryableByWhere<T>(List<IConditionalModel> conditionalModels) where T : class, new()
        {
            return GetCilentDb<T>().AsQueryable().Where(conditionalModels);
        }
        public ISugarQueryable<T> GetSugarQueryableByWhere<T>(Expression<Func<T, bool>> expression) where T : class, new()
        {
            return GetCilentDb<T>().AsQueryable().Where(expression);
        }
        public ISugarQueryable<T> GetSugarQueryableByWhere<T>(string whereString, object parameters = null) where T : class, new()
        {
            return GetCilentDb<T>().AsQueryable().Where(whereString, parameters);
        }
        /// <summary>
        /// 根据实体名称动态查询数据
        ///  //var list = db.Queryable<dynamic>().AS("order ").Where("id=@id", new { id = 1 }).ToList();//没实体一样
        /// </summary>
        /// <param name="tableName">表明</param>
        /// <param name="whereString">查询条件</param>
        /// <param name="parameters">查询参数</param>
        /// <returns></returns>
        public List<dynamic> GetListDataByDynamic(string tableName, string whereString, object parameters = null)
        {
            return Db().Queryable<dynamic>().AS(tableName).Where(whereString, parameters).ToList();
        }
        /// <summary>
        /// 根据Json串查询
        /// （[
        ///     {"FieldName":"id","ConditionalType":"0","FieldValue":"1"},
        ///     {"FieldName":"name","ConditionalType":"0","FieldValue":"jack"}
        ///  ]）
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <param name="json">json串</param>
        /// <returns></returns>
        public List<T> GetListDataByJsonConditional<T>(string json) where T : class, new()
        {
            var whererList = Db().Context.Utilities.JsonToConditionalModels(json);
            return GetListDataByWhere<T>(whererList);
        }
        /// <summary>
        /// 获取实体property的所有描述字段
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="excludeList"></param>
        /// <returns></returns>
        public List<EntityDescriptBind> GetPropertyListDescrity<T>(List<string> excludeDescriptionList = null) where T : class, new()
        {
            var entityInfo = Db().EntityMaintenance.GetEntityInfo<T>();
            List<EntityDescriptBind> propertyList = new List<EntityDescriptBind>();
            foreach (var item in entityInfo.Columns)
            {
                if ((excludeDescriptionList == null || !excludeDescriptionList.Contains(item.ColumnDescription)) && !item.ColumnDescription.IsEmpty())
                    propertyList.Add(new EntityDescriptBind() { FieldDescription = item.ColumnDescription, FieldName = item.PropertyInfo.Name });
            }
            return propertyList;
        }
        public List<T> GetListPage<T>(List<IConditionalModel> conditionalModels, int curPage, int pageSize, ref int _allcount) where T : class, new()
        {
            return GetSugarQueryableByWhere<T>(conditionalModels).ToPageList(curPage, pageSize, ref _allcount);
        }
        public List<T> GetListPage<T>(Expression<Func<T, bool>> expression, int curPage, int pageSize, ref int _allcount) where T : class, new()
        {
            return GetSugarQueryableByWhere<T>(expression).ToPageList(curPage, pageSize, ref _allcount);
        }
        public List<T> GetListPage<T>(string whereString,  int curPage, int pageSize, ref int _allcount, object parameters = null) where T : class, new()
        {
            return GetSugarQueryableByWhere<T>(whereString, parameters).ToPageList(curPage, pageSize, ref _allcount);
        }
        public List<IConditionalModel> GetSingleConditional(string FieldName, ConditionalType ConditionalType, string FieldValue)
        {
            return new List<IConditionalModel> {
                            new ConditionalModel { FieldName = FieldName, ConditionalType = ConditionalType, FieldValue = FieldValue }
            };
        }
        /// <summary>
        /// 获取表中的总数据量
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="conditionalModels"></param>
        /// <returns></returns>
        public int GetTabelCount<T>(List<IConditionalModel> conditionalModels) where T : class, new()
        {
            return GetCilentDb<T>().AsQueryable().Where(conditionalModels).Count();
        }
        /// <summary>
        /// 获取表中的max值
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <typeparam name="resultType">返回结果类型</typeparam>
        /// <param name="fieldName">属性名称</param>
        /// <returns></returns>
        public resultType GetTabelMax<T, resultType>(string fieldName) where T : class, new()
        {
            return GetCilentDb<T>().AsQueryable().Max<resultType>(fieldName);
        }
        /// <summary>
        /// 获取表中的min值
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <typeparam name="resultType">返回结果类型</typeparam>
        /// <param name="fieldName">属性名称</param>
        /// <returns></returns>
        public resultType GetTabelMin<T, resultType>(string fieldName) where T : class, new()
        {
            return GetCilentDb<T>().AsQueryable().Min<resultType>(fieldName);
        }
        /// <summary>
        /// 获取表中的Avg值
        /// </summary>
        /// <typeparam name="T">实体类型</typeparam>
        /// <typeparam name="resultType">返回结果类型</typeparam>
        /// <param name="fieldName">属性名称</param>
        /// <returns></returns>
        public resultType GetTabelAvg<T, resultType>(string fieldName) where T : class, new()
        {
            return GetCilentDb<T>().AsQueryable().Avg<resultType>(fieldName);
        }
        /// <summary>
        /// 获取树级实体列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="conditionalModels">查询条件</param>
        /// <returns></returns>
        public List<TreeEntity> GetTree<T>(List<IConditionalModel> conditionalModels) where T : class, new()
        {
            //第三个参数为0 表示 ： parentid为 0 设置成根目录, 正常情况下最顶层的parentid不是0就是null
            return GetCilentDb<TreeEntity>().AsQueryable().Where(conditionalModels).ToTree(item => item.Child, item => item.ParentId, 0);
        }
        /// <summary>
        /// 获取树级实体的所有父节点
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="primaryKeyValue">主键id</param>
        /// <returns></returns>
        public List<TreeEntity> GetTreeAllParentList<T>(object primaryKeyValue) where T : class, new()
        {
            return GetCilentDb<TreeEntity>().AsQueryable().ToParentList(item => item.ParentId, primaryKeyValue);//查主键上面所有
        }
        /// <summary>
        /// 获取树级实体的所有子节点
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="primaryKeyValue">主键id</param>
        /// <returns></returns>
        public List<TreeEntity> GetTreeAllChildList<T>(object primaryKeyValue) where T : class, new()
        {
            return GetCilentDb<TreeEntity>().AsQueryable().ToChildList(item => item.ParentId, primaryKeyValue);//查主键下面所有
        }

        /// <summary>
        /// 按照时间分别统计一年12个月的数据量
        /// </summary>
        /// <returns></returns>
        public object GetYearGroupMonthStatistics()
        {
            var queryableLeft = Db().Reportable(ReportableDateType.MonthsInLast1years).ToQueryable<DateTime>();
            var queryableRight = Db().Queryable<OperateInfoEntity>();
            var list = Db().Queryable(queryableLeft, queryableRight, JoinType.Left, (x1, x2) => x2.operate_time.ToString("yyyy-MM") == x1.ColumnName.ToString("yyyy-MM")).GroupBy((x1, x2) => x1.ColumnName).Select((x1, x2) => new
            {
                count = SqlFunc.AggregateSum(SqlFunc.IIF(x2.Id > 0, 1, 0)), //null的数据要为0所以不能用count
                date = x1.ColumnName.ToString("yyyy-MM")
            }).ToList();
            return list;
        }
        /// <summary>
        /// 统计某月每天的数量
        /// </summary>
        /// <param name="time"></param>
        /// <returns></returns>
        public object GetMonthGroupDayStatistics(DateTime time)
        {
            var days = (time.AddMonths(1) - time).Days;
            var dayArray = Enumerable.Range(1, days).Select(it => Convert.ToDateTime(time.ToString("yyyy-MM-" + it))).ToList();//转成时间数组
            var queryableLeft = Db().Reportable(dayArray).ToQueryable<DateTime>();
            var queryableRight = Db().Queryable<OperateInfoEntity>();
            var list = Db().Queryable(queryableLeft, queryableRight, JoinType.Left,
                (x1, x2) => x1.ColumnName.Date == x2.operate_time.Date)
                .GroupBy((x1, x2) => x1.ColumnName)
                .Select((x1, x2) => new
                {
                    count = SqlFunc.AggregateSum(SqlFunc.IIF(x2.Id > 0, 1, 0)),
                    day = x1.ColumnName.Day
                }).ToList();
            return list;
        }

        #endregion

        #region 事务
        public void BeginTran()
        {
            _db.Ado.BeginTran();
        }
        public void EndCommitTran()
        {
            try
            {
                _db.Ado.CommitTran();
            }
            catch (Exception ex)
            {
                _db.Ado.RollbackTran();
                throw ex;
            }
        }

        #endregion

        #region 插入
        /// <summary>
        /// 批量插入数据
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="listData">数据list集合</param>
        /// <returns>是否成功</returns>
        public bool InsertList<T>(List<T> listData) where T : class, new()
        {
            return GetCilentDb<T>().InsertRange(listData);
        }
      
        public Task<bool> InsertListAsync<T>(List<T> listData) where T : class, new()
        {
            return GetCilentDb<T>().InsertRangeAsync(listData);
        }
        /// <summary>
        /// 插入数据（不支持表情），返回插入行
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="listData">插入数据集合</param>
        /// <returns>插入行号</returns>
        public int InsertListReturnInsertCount<T>(List<T> listData) where T : class, new()
        {
          return  Db().Insertable(listData).ExecuteCommand();
        }
        /// <summary>
        ///  插入数据，返回插入行
        ///  优点：(500条以下速度最快，兼容所有类型和表情emoji)
        ///  缺点：500以上就开始慢了 （Oracle中500以上性能比第一种强），要加事务才能回滚 
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="ListData">插入数据集合</param>
        /// <returns></returns>
        public int InsertListMax500<T>(List<T> ListData) where T : class, new()
        {
            return Db().Insertable(ListData).UseParameter().ExecuteCommand();
        }
        /// <summary>
        /// 插入大数据用（1w以上数据量使用，非常快）
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="ListData">数据集合</param>
        /// <returns>插入条数</returns>
        public int InsertListBagData<T>(List<T> ListData) where T : class , new()
        {
            return Db().Fastest<T>().BulkCopy(ListData);
        }

        /// <summary>
        /// 插入单条数据
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="entity">数据实体</param>
        /// <returns>是否成功</returns>
        public bool InsertEntity<T>(T entity) where T : class, new()
        {
            return GetCilentDb<T>().Insert(entity);
        }
        public Task<bool> InsertEntityAsync<T>(T entity) where T : class, new()
        {
            return GetCilentDb<T>().InsertAsync(entity);
        }
        /// <summary>
        /// 插入数据，返回自增列id
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="entity">实体</param>
        /// <returns>主键</returns>
        public int InsertEntityReturnIdentity<T>(T entity) where T : class, new() {
            return Db().Insertable<T>(entity).ExecuteReturnIdentity();
        }
        #endregion

    }
}
